﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;

namespace SGArchiveSystem7x.Controllers.Statistics
{
    public class StatArchiveController : BaseController
    {
        string sControllerName = "StatArchive";
        // 档案统计: 默认为汇总统计
        public ActionResult Index()
        {
            var n = this.VaildUser(sControllerName);
            if (n != null)
                return n;

            return View();
        }

        /// <summary>
        /// 汇总表(年度与类型)
        /// </summary>
        /// <returns></returns>
        public ActionResult ViewArchive_NDandLX()
        {
            var list = SGArchiveSystem7xDB.Linq.T_ArchiveTreeDal.Get_ArchiveTree(0, 1);
            ViewBag.Columns = list;

            var listW = SGArchiveSystem7xDB.Sql.SGTongji.Tongji_NDandLX();

            if(listW != null)
            {
                ///获取所有年度
                List<string> listND = listW.Select(c => c.year).Distinct().OrderBy(c => c).ToList();
                ViewBag.ND = listND;
                ///所有数据
                ViewBag.W = listW;
            }

            return View();
        }

        [HttpPost]
        public string Set_ajaxXlsToNDandLX()
        {
            return "";
            //var list = SGArchiveSystem7xDB.Linq.T_ArchiveTreeDal.Get_ArchiveTree(0, 1);

            //var listW = SGArchiveSystem7xDB.Sql.SGTongji.Tongji_NDandLX();

            //if (listW != null)
            //{
            //    ///获取所有年度
            //    List<string> listND = listW.Select(c => c.year).Distinct().OrderBy(c => c).ToList();
            //    string SaveUrl = "~/Content/xls/" + DateTime.Now.Ticks.ToString() + ".xlsx";
            //    Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            //    Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;
            //    ///////////////////////////////////////////////////////////////////////////////////////////////////////////
            //    cells[0, 0].Value = "年度";
            //    int index = 0;
            //    foreach (var en in list)
            //    {
            //        cells[0, index++].Value = en.AR_NAME;
            //    }
            //        < td colspan = "4" align = "center" > @en.AR_NAME </ td >
            //</ tr >
            //< tr >
            //    @{
            //        foreach (var en in listColumns)
            //        {

            //        < td align = "center" > 永久 </ td >
 
            //         < td align = "center" > 30年 </ td >
      
            //              < td align = "center" > 10年 </ td >
           
            //                   < td align = "center" > 合计 </ td >
            //    }
            //    }
            //}
            //else
            //    return "";

            //return View();

            //Get_Data(int.Parse(listWhere[0]), listWhere[1], listWhere[2], out listHeader, out listStrs);

            //string SaveUrl = "~/Content/xls/" + DateTime.Now.Ticks.ToString() + ".xlsx";
            //Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            //Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;

            //int index = 1;
            //cells[0, 0].Value = "序号";
            //foreach (var header in listHeader)
            //{
            //    cells[0, index].Value = header;
            //    index++;
            //}
            //index = 1;
            //for (int i = 0; i < listStrs.Count; i++)
            //{
            //    cells[i + 1, 0].Value = index.ToString();
            //    int col = 1;
            //    foreach (var ss in listStrs[i])
            //    {
            //        cells[i + 1, col].Value = ss;
            //        col++;
            //    }
            //    index++;
            //}

            //workbook.Save(Server.MapPath(SaveUrl));
            //return SaveUrl.Replace("~", "..");
        }

        public ActionResult ViewArchive_NDandBM()
        {
            int count;
            var list = SGArchiveSystem7xDB.Linq.T_DeparetmentDal.Get_Department(out count);
            ViewBag.Columns = list;

            var listW = SGArchiveSystem7xDB.Sql.SGTongji.Tongji_NDandBM();

            if (listW != null)
            {
                ///获取所有年度
                List<string> listND = listW.Select(c => c.year).Distinct().OrderBy(c => c).ToList();
                ViewBag.ND = listND;
                ///所有数据
                ViewBag.W = listW;
            }

            return View();
        }

        public ActionResult ViewArchive_BMandLX()
        {
            var list = SGArchiveSystem7xDB.Linq.T_ArchiveTreeDal.Get_ArchiveTree(0, 1);
            ViewBag.Columns = list;

            var listW = SGArchiveSystem7xDB.Sql.SGTongji.Tongji_BMandLX();

            if (listW != null)
            {
                ///获取所有年度
                List<string> listND = listW.Select(c => c.year).Distinct().OrderBy(c => c).ToList();
                ViewBag.ND = listND;
                ///所有数据
                ViewBag.W = listW;
            }

            return View();
        }

        /// <summary>
        /// 自定义统计
        /// </summary>
        /// <returns></returns>
        public ActionResult Custom()
        {
            var n = this.VaildUser(sControllerName);
            if (n != null)
                return n;

            ///清除Page级的Session
            App_Code.CSession.Clear_PageSession(Session);

            ///先获取档案类型
            var list = Models.CComboboxModel.Create_ComboboxArchiveTree(false);
            ViewBag.Items = list;
            return View();
        }

        [HttpPost]
        public ActionResult CustomBuildComboboxFields(int pid)
        {
            var listType = SGArchiveSystem7xDB.Linq.T_ArchiveFieldsDal.Get_ArchiveFieldsForArchive(pid);
            var list = listType.Where(c => c.AF_STAT == 1 && c.AF_STATUS == 1).Select(c => new { c.AF_FIELD, c.AF_WORDID, c.AF_NAME,c.AF_TYPE }).ToList();
            List<StatTempCmbClass> listN = new List<StatTempCmbClass>();
            foreach (var n in list)
            {
                StatTempCmbClass en = new StatTempCmbClass();
                en.Text = n.AF_NAME;
                en.Value = n.AF_FIELD + "|" + n.AF_TYPE + "|" + (n.AF_WORDID == null ? "0" : n.AF_WORDID.ToString()) + "|" + n.AF_NAME;
                listN.Add(en);
            }
            return Json(listN, JsonRequestBehavior.AllowGet);
        }

        public ActionResult StartStat(int arid, string f1, string f2)
        {
            List<string> listHeader;
            List<string[]> listStrs;

            List<string> listWhere = new List<string>();
            listWhere.Add(arid.ToString());
            listWhere.Add(f1);
            listWhere.Add(f2);
            SGArchiveSystem7x.App_Code.CSession.Add_QueryWhere(Session, listWhere);

            Get_Data(arid, f1, f2, out listHeader, out listStrs);
            ViewBag.header = listHeader;
            ViewBag.row = listStrs;

            return View();
        }
        #region 不用了
        /*
        public ActionResult StartStat(int arid, string f1, string f2)
        {
            //System.Data.DataTable dt = SGArchiveSystem7xDB.Sql.SGTongji.Tongji_Archive("t_archiveaj", f1, f2, arid);
            //18;1;WS_QZ;XXXX;全宗号
            //id + '|' + type + "|" + wordid + "|" + name
            string[] strs1 = f1.Split('|');
            string[] strs2 = f2.Split('|');
            string s1 = strs1[0];
            string s2 = strs2[0];


            bool bW1 = false;
            bool bW2 = false;
            if (strs1[2] != "0")
            {
                ///即词典
                bW1 = true;
            }

            if (strs2[2] != "0")
            {
                ///即词典
                bW2 = true;
            }

            

            int count = 0;
            ///获取所有的词典类型
            var listWord =  SGArchiveSystem7xDB.Linq.T_ArchiveWordDal.Get_ArchiveWord(out count);
            ///获取所有的部门信息
            var listDep = SGArchiveSystem7xDB.Linq.T_DeparetmentDal.Get_Department(out count);


            //List<T_SYSWD> listWord = new List<T_SYSWD>();
            //if (bW1 || bW2)
            //    listWord = TSysAr_LinqDal.Query_Word(null, 1).ToList();

            var dt = SGArchiveSystem7xDB.Sql.SGTongji.Tongji_Archive("T_ArchiveAj", s1, s2, arid);
            List<string> listHeader = new List<string>();
            List<string> listRow = new List<string>();
            foreach (DataRow dr in dt.Rows)
            {
                listHeader.Add(dr["F2"].ToString());
                listRow.Add(dr["F1"].ToString());
            }
            listHeader = listHeader.Distinct().ToList();
            listRow = listRow.Distinct().ToList();

            listHeader.Insert(0, strs1[3]);
            listHeader.Add("合计");

            List<string[]> listStrs = new List<string[]>();
            int[] ints = new int[listHeader.Count];

            foreach (var s in listRow)
            {
                int count2 = 0;
                string[] strs = new string[listHeader.Count];

                strs[0] = s;
                for (int i = 1; i < listHeader.Count; i++)
                {
                    strs[i] = "0";
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["F1"].ToString() == s && dr["F2"].ToString() == listHeader[i])
                        {
                            strs[i] = dr["n"].ToString();
                            count2 += int.Parse(strs[i]);
                            ints[i] += int.Parse(strs[i]);
                            break;
                        }
                    }
                }
                strs[listHeader.Count - 1] = count2.ToString();
                ints[listHeader.Count - 1] += count2;
                listStrs.Add(strs);
            }
            string[] output = Array.ConvertAll<int, string>(ints, i => i.ToString());
            listStrs.Add(output);

            if (bW2)
            {
                foreach (var n in listWord)
                {
                    for (int i = 0; i < listHeader.Count; i++)
                    {
                        if (n.WD_ID.ToString() == listHeader[i])
                        {
                            listHeader[i] = n.WD_NAME;
                            break;
                        }
                    }
                }
            }
            if (bW1)
            {
                foreach (var n in listWord)
                {
                    for (int i = 0; i < listStrs.Count; i++)
                    {
                        if (n.WD_ID.ToString() == listStrs[i][0])
                        {
                            listStrs[i][0] = n.WD_NAME;
                            break;
                        }
                    }
                }
            }

            ///ViewBag.row = listRow;
            ViewBag.header = listHeader;
            ViewBag.row = listStrs;

            return View();
        }
        */
        #endregion

        [HttpPost]
        public string StartStatXls()
        {
            List<string> listHeader;
            List<string[]> listStrs;
            var listWhere = SGArchiveSystem7x.App_Code.CSession.Get_QueryWhere(Session);

            if (listWhere == null || listWhere.Count == 0)
                return "-1";

            Get_Data(int.Parse(listWhere[0]), listWhere[1], listWhere[2], out listHeader, out listStrs);

            string SaveUrl = "~/Content/xls/" + DateTime.Now.Ticks.ToString() + ".xlsx";
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;

            int index = 1;
            cells[0, 0].Value = "序号";
            foreach (var header in listHeader)
            {
                cells[0, index].Value = header;
                index++;
            }
            index = 1;
            for (int i = 0; i < listStrs.Count; i++)
            {
                cells[i + 1, 0].Value = index.ToString();
                int col = 1;
                foreach (var ss in listStrs[i])
                {
                    cells[i + 1, col].Value = ss;
                    col++;
                }
                index++;
            }

            workbook.Save(Server.MapPath(SaveUrl));
            return SaveUrl.Replace("~", "..");
        }

        private void Get_Data(int arid, string f1, string f2, out List<string> listHeader, out List<string[]> listStrs)
        {
            string[] strs1 = f1.Split('|');
            string[] strs2 = f2.Split('|');
            string s1 = strs1[0];
            string s2 = strs2[0];


            bool bW1 = false;
            bool bW2 = false;
            bool bM1 = false;
            bool bM2 = false;
            if (strs1[1] == "词典")
            {
                ///即词典
                bW1 = true;
            }else if(strs1[1] == "部门")
            {
                bM1 = true;
            }
            if (strs2[1] == "词典")
            {
                ///即词典
                bW2 = true;
            }
            else if (strs2[1] == "部门")
            {
                bM2 = true;
            }
            int count = 0;
            ///获取所有的词典类型
            var listWord = SGArchiveSystem7xDB.Linq.T_ArchiveWordDal.Get_ArchiveWord(out count);
            ///获取所有的部门信息
            var listDep = SGArchiveSystem7xDB.Linq.T_DeparetmentDal.Get_Department(out count);


            //List<T_SYSWD> listWord = new List<T_SYSWD>();
            //if (bW1 || bW2)
            //    listWord = TSysAr_LinqDal.Query_Word(null, 1).ToList();

            var dt = SGArchiveSystem7xDB.Sql.SGTongji.Tongji_Archive("T_ArchiveAj", s1, s2, arid);
            listHeader = new List<string>();
            var listRow = new List<string>();
            foreach (DataRow dr in dt.Rows)
            {
                listHeader.Add(dr["F2"].ToString());
                listRow.Add(dr["F1"].ToString());
            }
            listHeader = listHeader.Distinct().ToList();
            listRow = listRow.Distinct().ToList();

            listHeader.Insert(0, strs1[3]);
            listHeader.Add("合计");

            listStrs = new List<string[]>();
            int[] ints = new int[listHeader.Count];

            foreach (var s in listRow)
            {
                int count2 = 0;
                string[] strs = new string[listHeader.Count];

                strs[0] = s;
                for (int i = 1; i < listHeader.Count; i++)
                {
                    strs[i] = "0";
                    foreach (DataRow dr in dt.Rows)
                    {
                        if (dr["F1"].ToString() == s && dr["F2"].ToString() == listHeader[i])
                        {
                            strs[i] = dr["n"].ToString();
                            count2 += int.Parse(strs[i]);
                            ints[i] += int.Parse(strs[i]);
                            break;
                        }
                    }
                }
                strs[listHeader.Count - 1] = count2.ToString();
                ints[listHeader.Count - 1] += count2;
                listStrs.Add(strs);
            }
            string[] output = Array.ConvertAll<int, string>(ints, i => i.ToString());
            listStrs.Add(output);

            if (bW2)
            {
                foreach (var n in listWord)
                {
                    for (int i = 0; i < listHeader.Count; i++)
                    {
                        if (n.WD_ID.ToString() == listHeader[i])
                        {
                            listHeader[i] = n.WD_NAME;
                            break;
                        }
                    }
                }
            }
            if (bW1)
            {
                foreach (var n in listWord)
                {
                    for (int i = 0; i < listStrs.Count; i++)
                    {
                        if (n.WD_ID.ToString() == listStrs[i][0])
                        {
                            listStrs[i][0] = n.WD_NAME;
                            break;
                        }
                    }
                }
            }

            if (bM2)
            {
                foreach (var n in listDep)
                {
                    for (int i = 0; i < listHeader.Count; i++)
                    {
                        if (n.Dep_ID.ToString() == listHeader[i])
                        {
                            listHeader[i] = n.Dep_Name;
                            break;
                        }
                    }
                }
            }
            if (bM1)
            {
                foreach (var n in listDep)
                {
                    for (int i = 0; i < listStrs.Count; i++)
                    {
                        if (n.Dep_ID.ToString() == listStrs[i][0])
                        {
                            listStrs[i][0] = n.Dep_Name;
                            break;
                        }
                    }
                }
            }
        }


        [HttpPost]
        public string StartStatXls2()
        {
            List<string> listHeader;
            List<string[]> listStrs;
            var listWhere = SGArchiveSystem7x.App_Code.CSession.Get_QueryWhere(Session);

            if (listWhere == null || listWhere.Count == 0)
                return "-1";

            Get_Data(int.Parse(listWhere[0]), listWhere[1], listWhere[2], out listHeader, out listStrs);

            string SaveUrl = "~/Content/xls/" + DateTime.Now.Ticks.ToString() + ".xlsx";
            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook();
            Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;

            int index = 1;
            cells[0, 0].Value = "序号";
            foreach (var header in listHeader)
            {
                cells[0, index].Value = header;
                index++;
            }
            index = 1;
            for (int i = 0; i < listStrs.Count; i++)
            {
                cells[i + 1, 0].Value = index.ToString();
                int col = 1;
                foreach (var ss in listStrs[i])
                {
                    cells[i + 1, col].Value = ss;
                    col++;
                }
                index++;
            }

            workbook.Save(Server.MapPath(SaveUrl));
            return SaveUrl.Replace("~", "..");
        }
    }
}

/// <summary>
/// 临时的显示类，目前只用在统计Controller和View里
/// </summary>

public class StatTempCmbClass
{
    public string Value { get; set; }
    public string Text { get; set; }
}